package org.dbutils.dao.impl;

import org.dbutils.dao.StaffDao;
import org.dbutils.entity.Staffs;
import org.dbutils.util.JdbcDbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ZJL
 * Staffs 接口的实现 - 基于原生 JDBC 的实现
 * @date 2025-02-25
 */
public class StaffDaoImplJdbc implements StaffDao {

    @Override
    public int addStaff(Staffs staff) {
        int row = 0;
        String sql = "insert into staffs(name,age,phone,sta_pos) values (?,?,?,?);";
        Connection connection = JdbcDbUtils.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, staff.getName());
            preparedStatement.setInt(2, staff.getAge());
            preparedStatement.setString(3, staff.getPhone());
            preparedStatement.setString(4, staff.getStaPos());
            row = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcDbUtils.close(null, preparedStatement, connection);
        }
        return row;
    }

    @Override
    public int updateStaff(Staffs staff) {
        int row = 0;
        String sql = "update staffs set name = ?,age = ?,phone = ?,sta_pos = ?,update_time = ? where id = ?;";
        Connection connection = JdbcDbUtils.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, staff.getName());
            preparedStatement.setInt(2, staff.getAge());
            preparedStatement.setString(3, staff.getPhone());
            preparedStatement.setString(4, staff.getStaPos());
            preparedStatement.setObject(5,staff.getUpdateTime());
            preparedStatement.setInt(6, staff.getId());
            row = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcDbUtils.close(null, preparedStatement, connection);
        }
        return row;
    }

    @Override
    public int deleteStaff(int id) {
        int row = 0;
        String sql = "delete from staffs where id=?";
        Connection connection = JdbcDbUtils.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            row = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcDbUtils.close(null, preparedStatement, connection);
        }
        return row;
    }

    @Override
    public List<Staffs> staffsList(int currenPage, int pageSize) {
        List<Staffs> list = new ArrayList<Staffs>();
        String sql = "select id,name,age,phone,sta_pos,add_time,update_time from staffs limit ?,?;";
        Connection connection = JdbcDbUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            //当前页
            int limit = (currenPage - 1) * pageSize;
            preparedStatement.setInt(1, limit);
            preparedStatement.setInt(2, pageSize);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Staffs staff = new Staffs();
                staff.setId(resultSet.getInt("id"));
                staff.setName(resultSet.getString("name"));
                staff.setAge(resultSet.getInt("age"));
                staff.setPhone(resultSet.getString("phone"));
                staff.setStaPos(resultSet.getString("sta_pos"));
                staff.setAddTime(resultSet.getTimestamp("add_time").toLocalDateTime());
                staff.setUpdateTime(resultSet.getTimestamp("update_time").toLocalDateTime());
                list.add(staff);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcDbUtils.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    @Override
    public Staffs selectStaffById(int id) {
        Staffs staff = null;
        String sql = "select id,name,age,phone,sta_pos,add_time,update_time from staffs where id=?";
        Connection connection = JdbcDbUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            //当前页
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                staff = new Staffs();
                staff.setId(resultSet.getInt("id"));
                staff.setName(resultSet.getString("name"));
                staff.setAge(resultSet.getInt("age"));
                staff.setPhone(resultSet.getString("phone"));
                staff.setStaPos(resultSet.getString("sta_pos"));
                staff.setAddTime(resultSet.getTimestamp("add_time").toLocalDateTime());
                staff.setUpdateTime(resultSet.getTimestamp("update_time").toLocalDateTime());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcDbUtils.close(resultSet, preparedStatement, connection);
        }
        return staff;
    }
}
